﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace Sami_SMS_CRM_Business
{
    public class CollectionRepository : Repository
    {
        public IList<crm_collection> GetAll()
        {
            return DB.crm_collections.ToList();
        }

        public IList<crm_collection> GetAll(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = DB.crm_collections.Skip((currentPage - 1) * pageSize).Take(pageSize);
            totalRecord = DB.crm_collections.Count();
            return retValue.ToList();
        }

        public void DeleleById(int Id)
        {
            var entity = DB.crm_collections.FirstOrDefault(p => p.collection_id == Id);
            if (entity != null)
            {
                DB.crm_collections.DeleteOnSubmit(entity);
                DB.SubmitChanges();
            }
        }

        public crm_collection GetById(int Id)
        {
            return DB.crm_collections.FirstOrDefault(p => p.collection_id == Id);
        }

        public int AddNew(crm_collection entity)
        {
            DB.crm_collections.InsertOnSubmit(entity);
            DB.SubmitChanges();
            return entity.collection_id;
        }

        public void Update(crm_collection entity)
        {
            DB.SubmitChanges();
        }

        public string GetQuery(crm_collection entity)
        {
            if (entity.collection_value != null)
            {
                XElement element = entity.collection_value;
                StringBuilder retValue = new StringBuilder("SELECT ");
                string selectClause = element.Element("select").Value;
                string fromClause = element.Element("from").Value;
                string whereClause = element.Element("where").Value;
                retValue.AppendLine(selectClause);
                retValue.AppendLine("FROM " + fromClause);
                if (!string.IsNullOrEmpty(whereClause))
                    retValue.AppendLine("WHERE " + whereClause);
                return retValue.ToString();
            }
            return string.Empty;
        }

        public DataTable GetSubscriber(int Id)
        {
            DataTable retTable = new DataTable();
            var entity = GetById(Id);
            if (entity == null || entity.collection_value == null) return null;
            string query = "SELECT T1.subscriber, cs.full_name, cs.date_of_birth, T1.operator_id, \n" +
                            "T1.gateway_id, cs.note ,cs.[enabled] \n" +
                            " FROM ( \n" +
                            GetQuery(entity) +
                            ") AS T1  \n" +
                            "LEFT JOIN  \n" +
                            "crm_subscriber cs ON T1.subscriber = cs.subscriber \n" +
                            "ORDER BY cs.seq_id";
            using (SqlDataAdapter da = new SqlDataAdapter(query, Settings.LocalSettings.ConnectionString))
            {
                da.Fill(retTable);
            }
            return retTable;
        }

        public DataTable GetDataByQuery(string query)
        {
            try
            {
                DataTable retTable = new DataTable();
                using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.Text;
                        retTable.Load(cmd.ExecuteReader());
                    }
                }
                return retTable;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
